package com.example.project10;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

public class UserDatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "users.db";
    private static final int DATABASE_VERSION = 1;
    
    // 表名和列名
    private static final String TABLE_USERS = "users";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_EMAIL = "email";
    private static final String COLUMN_FIRST_NAME = "first_name";
    private static final String COLUMN_LAST_NAME = "last_name";
    private static final String COLUMN_AVATAR = "avatar";
    
    // 创建表的SQL语句
    private static final String CREATE_TABLE_USERS = 
            "CREATE TABLE " + TABLE_USERS + " (" +
            COLUMN_ID + " INTEGER PRIMARY KEY, " +
            COLUMN_EMAIL + " TEXT, " +
            COLUMN_FIRST_NAME + " TEXT, " +
            COLUMN_LAST_NAME + " TEXT, " +
            COLUMN_AVATAR + " TEXT" + ")";
    
    public UserDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_USERS);
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
        onCreate(db);
    }
    
    /**
     * 插入或更新用户数据
     */
    public void insertOrUpdateUsers(List<User> users) {
        SQLiteDatabase db = this.getWritableDatabase();
        
        // 开始事务以提高性能
        db.beginTransaction();
        try {
            // 先清空表
            db.delete(TABLE_USERS, null, null);
            
            // 插入所有用户
            for (User user : users) {
                ContentValues values = new ContentValues();
                values.put(COLUMN_ID, user.getId());
                values.put(COLUMN_EMAIL, user.getEmail());
                values.put(COLUMN_FIRST_NAME, user.getFirstName());
                values.put(COLUMN_LAST_NAME, user.getLastName());
                values.put(COLUMN_AVATAR, user.getAvatar());
                
                db.insertWithOnConflict(TABLE_USERS, null, values, SQLiteDatabase.CONFLICT_REPLACE);
            }
            
            // 设置事务成功
            db.setTransactionSuccessful();
        } finally {
            // 结束事务
            db.endTransaction();
        }
    }
    
    /**
     * 获取所有用户数据
     */
    public List<User> getAllUsers() {
        List<User> users = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursor = db.query(TABLE_USERS, null, null, null, null, null, COLUMN_ID + " ASC");
        
        if (cursor.moveToFirst()) {
            do {
                User user = new User();
                user.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ID)));
                user.setEmail(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_EMAIL)));
                user.setFirstName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FIRST_NAME)));
                user.setLastName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_LAST_NAME)));
                user.setAvatar(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_AVATAR)));
                
                users.add(user);
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        return users;
    }
    
    /**
     * 清空用户表
     */
    public void clearUsers() {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_USERS, null, null);
    }
}